from sqlalchemy import text

import google.protobuf.json_format as json_format

from coin.support.exchange_status.logic.db_connector import \
  ExchangeStatusConnector
from coin.proto.exchange_status_pb2 import (RestStatusProto,
                                            FeedStatusProto,
                                            OrderStatusProto,
                                            ExchangeStatusProto)


class ExchangeStatusQuerier(ExchangeStatusConnector):
  def __init__(self, *args, **kwargs):
    super().__init__(*args, **kwargs)

  def query_rest_status_history(self, request_list):
    result_list = []
    session = self._connection
    for request in request_list:
      try:
        sql = self.gen_query_rest_status_history_sql(request)
        result = session.query(self._RestStatus).from_statement(text(sql)).\
                         params(exchange_api_id=request.exchange_api_id,
                                account_id=request.account_id,
                                sybmol=request.symbol,
                                hostname=request.hostname).all()
        assert len(result) == 1, len(result)
        rest_status = RestStatusProto()
        result_list.append(json_format.Parse(result[0].rest_status_response, rest_status))
      except Exception as e:
        self._logger.error('Fail to query rest status. %s %s %s', request, type(e), e)
        result_list.append(None)
        continue
    return result_list

  def query_feed_status_history(self, request_list):
    result_list = []
    session = self._connection
    for request in request_list:
      try:
        sql = self.gen_query_feed_status_history_sql(request)
        result = session.query(self._FeedStatus).from_statement(text(sql)).\
                         params(exchange_api_id=request.exchange_api_id,
                                symbol=request.symbol,
                                hostname=request.hostname,
                                feed_name=request.feed_name).all()
        assert len(result) == 1, len(result)
        feed_status = FeedStatusProto
        result_list.append(json_format.Parse(result[0].feed_status_response, feed_status))
      except Exception as e:
        self._logger.error('Fail to query feed status. %s %s %s', request, type(e), e)
        result_list.append(None)
        continue
    return result_list

  def query_order_status_history(self, request_list):
    result_list = []
    session = self._connection
    for request in request_list:
      try:
        sql = self.gen_query_order_status_history_sql(request)
        result = session.query(self._OrderStatus).from_statement(text(sql)).\
                         params(exchange_api_id=request.exchange_api_id,
                                account_id=request.account_id,
                                symbol=request.symbol,
                                hostname=request.hostname).all()
        assert len(result) == 1, len(result)
        order_status = OrderStatusProto
        result_list.append(json_format.Parse(result[0].order_status_response, order_status))
      except Exception as e:
        self._logger.error('Fail to query order status. %s %s %s', request, type(e), e)
        result_list.append(None)
        continue
    return result_list

  def query_exchange_status_history(self, request_list):
    result_list = []
    session = self._connection
    for request in request_list:
      try:
        sql = self.gen_query_exchange_status_history_sql(request)
        result = session.query(self._ExchangeStatus).from_statement(text(sql)).\
                         params(exchange_api_id=request.exchange_api_id,
                                account_id=request.account_id,
                                symbol=request.symbol,
                                hostname=request.hostname).all()
        assert len(result) == 1, len(result)
        exchange_status = ExchangeStatusProto
        result_list.append(json_format.Parse(result[0].exchange_status_response, exchange_status))
      except Exception as e:
        self._logger.error('Fail to query exchange status. %s %s %s', request, type(e), e)
        result_list.append(None)
        continue
    return result_list

  @staticmethod
  def gen_query_rest_status_history_sql(request):
    sql = """
SELECT RestStatusHistory.rest_status_response
FROM (
  SELECT MAX(hid)
  FROM RestStatusHistory
  WHERE exchange_api_id = :exchange_api_id
  AND account_id = :account_id
  AND symbol = :symbol
  AND hostname = :hostname
  AND TIMEDIFF(NOW(), query_timestamp) < '00:01:00') AS X
JOIN RestStatusHistory
ON X.hid = RestStatusHistory.hid;
"""
    return sql

  @staticmethod
  def gen_query_feed_status_history_sql(request):
    sql = """
SELECT FeedStatusHistory.feed_status_response
FROM (
  SELECT MAX(hid)
  FROM FeedStatusHistory
  WHERE exchange_api_id = :exchange_api_id
  AND symbol = :symbol
  AND hostname = :hostname
  AND feed_name = :feed_name
  AND TIMEDIFF(NOW(), query_timestamp) < '00:01:00') AS X
JOIN FeedStatusHistory
ON X.hid = FeedStatusHistory.hid;
"""
    return sql

  @staticmethod
  def gen_query_order_status_history_sql(request):
    sql = """
SELECT OrderStatusHistory.order_status_response
FROM (
  SELECT MAX(hid)
  FROM OrderStatusHistory
  WHERE exchange_api_id = :exchange_api_id
  AND account_id = :account_id
  AND symbol = :symbol
  AND hostname = :hostname
  AND TIMEDIFF(NOW(), query_timestamp) < '00:01:00') AS X
JOIN OrderStatusHistory
ON X.hid = OrderStatusHistory.hid;
"""
    return sql

  @staticmethod
  def gen_query_exchange_status_history_sql(request):
    sql = """
SELECT ExchangeStatusHistory.exchange_status_response
FROM (
  SELECT MAX(hid)
  FROM ExchangeStatusHistory
  WHERE exchange_api_id = :exchange_api_id
  AND account_id = :account_id
  AND symbol = :symbol
  AND hostname = :hostname
  AND TIMEDIFF(NOW(), query_timestamp) < '00:01:00') AS X
JOIN ExchangeStatusHistory
ON X.hid = ExchangeStatusHistory.hid;
"""
    return sql
